import os
import sys
import time
import shutil
import pymysql
import requests
import warnings
import datetime
import pandas as pd
from selenium import webdriver
from sql_insert import csv_to_sql
from dbutils.pooled_db import PooledDB
from data_check import check_csv_result
from ssh_operate import get_web_password
from selenium.webdriver.common.by import By
from selenium.webdriver.chrome.service import Service
from global_parameter import DB_NAME, chromedriver_path, txt_path

# 忽略警告信息
warnings.filterwarnings('ignore')


class Logger(object):
    """
    封装Logger类，用于系统print的时候，自动写入到log文件中
    """

    def __init__(self, fileN='Default.log'):
        self.terminal = sys.stdout
        self.log = open(fileN, 'w')

    def write(self, message):
        '''print实际相当于sys.stdout.write'''
        self.terminal.write(message)
        self.log.write(message)

    def flush(self):
        pass


def setDir(filepath):
    '''
    如果文件夹不存在就创建
    :param filepath:需要创建的文件夹路径
    :return:
    '''
    if not os.path.exists(filepath):
        os.mkdir(filepath)


setDir("./logs")

sys.stdout = Logger('./logs/log.txt')  # 调用print时相当于Logger().write()

# 清空csv_results
if os.path.exists("./csv_results"):
    # 如果文件夹存在，则使用 shutil.rmtree() 删除文件夹及其内容
    shutil.rmtree("./csv_results")
if os.path.exists("./txt_result"):
    # 如果文件夹存在，则使用 shutil.rmtree() 删除文件夹及其内容
    shutil.rmtree("./txt_result")
os.mkdir("./csv_results")
os.mkdir("./txt_result")
print("清空csv_results文件夹")
print("清空txt_result文件夹")


def get_cookie(browser, ip, port, username, password):
    """
    使用selenium登录网页，获取cookie，供后续接口调用
    :param browser:
    :param ip:
    :param port:
    :param username:
    :param password:
    :return:
    """
    browser.get("http://{}:{}/login".format(ip, port))
    time.sleep(1)
    browser.find_element(By.NAME, 'username').send_keys(username)  # 填入用户名
    time.sleep(1)
    browser.find_element(By.NAME, 'password').send_keys(password)  # 填入密码
    time.sleep(1)
    browser.find_element(By.ID, "btnSubmit").click()  # 填入密码
    time.sleep(1)

    cookies_list = browser.get_cookies()
    cookie_value = cookies_list[0].get("value")
    print(cookie_value)
    time.sleep(1)
    return cookie_value


def time_drive_task(ip, port, cookie_value, startTime="", endTime=""):
    """
    触发时间驱动计算电压数据
    :param ip:
    :param port:
    :param cookie_value:
    :return:
    """
    headers = {
        "Accept": "*/*",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",
        "Connection": "keep-alive",
        "Content-Length": "0",
        "Cookie": "JSESSIONID={}".format(cookie_value),
        "Host": "{}:{}".format(ip, port),
        "Origin": "http://{}:{}".format(ip, port),
        "Referer": "http://{}:{}/monitor/job".format(ip, port),
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36 Edg/111.0.1661.44",
        "X-Requested-With": "XMLHttpRequest"
    }
    try:
        body = {'startTime': startTime, 'endTime': endTime}  # 参数为dict形式
        print(body)
        requests.post("http://{}:{}/timeDrivenTask/taskService".format(ip, port), headers=headers, timeout=5, data=body)
    except:
        pass
    # print(res)
    # print("请求定时任务返回状态码：{}".format(res.status_code))
    # assert res.status_code == 200


def stop_calc(ip, port, password):
    """
    触发时间驱动计算电压数据
    :param ip:
    :param port:
    :param cookie_value:
    :return:
    """
    service = Service(chromedriver_path)
    option = webdriver.ChromeOptions()
    # 不弹出chrome浏览器
    option.add_argument("headless")
    browser = webdriver.Chrome(service=service, options=option)
    cookie_value = get_cookie(browser, ip, port, "admin", password)
    browser.close()

    headers = {
        "Accept": "*/*",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",
        "Connection": "keep-alive",
        "Content-Length": "0",
        "Cookie": "JSESSIONID={}".format(cookie_value),
        "Host": "{}:{}".format(ip, port),
        "Origin": "http://{}:{}".format(ip, port),
        "Referer": "http://{}:{}/monitor/job".format(ip, port),
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36 Edg/111.0.1661.44",
        "X-Requested-With": "XMLHttpRequest"
    }
    try:
        requests.post("http://{}:{}/timeDrivenTask/stopCalculate".format(ip, port), headers=headers, timeout=5)
    except:
        pass

    # 停止计算超时时间60分钟
    for i in range(6 * 60):
        res_str = get_calc_status(ip, port, cookie_value)
        if res_str == "false":
            break
        if i == 0 and res_str == "true":
            truncate_mysql_tables(ip, DB_NAME)
        time.sleep(10)
        print("停止计算中，等待计算完成，已等待时间：{}".format((i + 1) * 10))


def get_calc_status(ip, port, cookie_value):
    """
    获取计算状态
    :param ip:
    :param port:
    :param cookie_value:
    :return:
    """
    headers = {
        "Accept": "*/*",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",
        "Connection": "keep-alive",
        "Content-Length": "0",
        "Cookie": "JSESSIONID={}".format(cookie_value),
        "Host": "{}:{}".format(ip, port),
        "Origin": "http://{}:{}".format(ip, port),
        "Referer": "http://{}:{}/monitor/job".format(ip, port),
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36 Edg/111.0.1661.44",
        "X-Requested-With": "XMLHttpRequest"
    }
    try:
        res = requests.post("http://{}:{}/timeDrivenTask/getCalculateStatus".format(ip, port), headers=headers,
                            timeout=5)
    except:
        pass
    res_str = str(res.content, encoding="utf-8")
    # print(res_str)
    return res_str


def truncate_mysql_tables(DB_HOST, DB_NAME):
    """
    截断数据表，用于停止计算用
    :param DB_HOST:
    :param DB_NAME:
    :param channel_config:
    :return:
    """
    POOL = PooledDB(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                    maxusage=None, setsession=[], ping=0, host=DB_HOST, port=3306, user='oetsky', password='Oetsky@123',
                    database=DB_NAME, charset='utf8')
    conn = POOL.connection()
    cursor = conn.cursor()

    cursor.execute("TRUNCATE dv_voltage_channel;")
    cursor.execute("TRUNCATE dv_voltage_line_group_channel;")
    cursor.execute("TRUNCATE dv_voltage_line_group;")
    cursor.execute("TRUNCATE algo_config;")
    cursor.execute("TRUNCATE fs_voltage_transformer;")
    cursor.execute("TRUNCATE da_voltage_data_temp;")

    cursor.execute("TRUNCATE err_voltage_error;")
    cursor.execute("TRUNCATE err_voltage_status;")
    cursor.execute("TRUNCATE err_voltage_avg;")
    cursor.execute("TRUNCATE err_voltage_slide_window;")
    cursor.execute("TRUNCATE err_voltage_error_pile;")
    cursor.execute("TRUNCATE err_voltage_matrix_status;")
    cursor.execute("TRUNCATE err_voltage_pre_pile_record;")
    cursor.execute("TRUNCATE err_voltage_dynamic_slide_window;")
    print("停止计算，清空所有表")

    cursor.close()
    conn.close()


def start_time_task(ip, port, username, password, min_time, max_time):
    """
    启动定时任务
    :param ip:
    :param port:
    :param username:
    :param password:
    :return:
    """
    service = Service(chromedriver_path)
    option = webdriver.ChromeOptions()
    # 不弹出chrome浏览器
    option.add_argument("headless")
    browser = webdriver.Chrome(service=service, options=option)
    cookie_value = get_cookie(browser, ip, port, username, password)
    time_drive_task(ip, port, cookie_value, min_time, max_time)
    browser.close()


def wait_task_done(DB_HOST, DB_NAME, datatime_max, is_cal_today=False):
    """
    等待定时任务完成
    :param DB_HOST:
    :param DB_NAME:
    :param is_cal_today:
    :return:
    """
    for i in range(6 * 20 * 60):
        # 计算超过一个小时停止
        # for i in range(6 * 60):
        con_user = pymysql.connect(host=DB_HOST, port=3306, user="oetsky", password='Oetsky@123', db=DB_NAME,
                                   charset='utf8')
        if is_cal_today:
            str_today = datetime.date.today().strftime("%Y-%m-%d")
            df_err_voltage_error = pd.read_sql(
                "SELECT * FROM err_voltage_error where calculate_time like'{}%';".format(str_today), con_user)
        else:
            df_err_voltage_error = pd.read_sql("SELECT * FROM err_voltage_error;", con_user)
        calculate_time_list = df_err_voltage_error["calculate_time"].tolist()
        final_time = (datatime_max + datetime.timedelta(hours=23) + datetime.timedelta(minutes=59)).strftime(
            "%Y-%m-%d %H:%M:%S")
        calculate_time_list = [i.strftime("%Y-%m-%d %H:%M:%S") for i in calculate_time_list]

        if final_time not in calculate_time_list:
            time.sleep(10)
            if calculate_time_list:
                print("定时任务已经执行时间：{}s,需要生成最后时间：{},实际当前生成最后时间：{}".format(
                    (i + 1) * 10, final_time, calculate_time_list[-1]))
            else:
                print("定时任务已经执行时间：{}s,需要生成最后时间：{},实际暂无数据产生".format((i + 1) * 10, final_time))
            con_user.close()
            continue
        else:
            time.sleep(10)
            con_user.close()
            break


def export_csv(DB_HOST, DB_NAME, scene_name):
    """
    读取SQL，导出csv
    :param DB_HOST:
    :param DB_NAME:
    :return:
    """
    os.mkdir("./csv_results/{}".format(scene_name))
    str_now_time = datetime.datetime.now().strftime("%Y_%m_%d_%H_%M_%S")
    con_user = pymysql.connect(host=DB_HOST, port=3306, user="oetsky", password='Oetsky@123', db=DB_NAME,
                               charset='utf8')
    # 获取数据库数据为dataframe
    algo_config = pd.read_sql("SELECT * FROM algo_config;", con_user)
    da_voltage_data_temp = pd.read_sql("SELECT * FROM da_voltage_data_temp;", con_user)
    dv_voltage_channel = pd.read_sql("SELECT * FROM dv_voltage_channel;", con_user)
    dv_voltage_line_group_channel = pd.read_sql("SELECT * FROM dv_voltage_line_group_channel;", con_user)
    dv_voltage_line_group = pd.read_sql("SELECT * FROM dv_voltage_line_group;", con_user)
    fs_voltage_transformer = pd.read_sql("SELECT * FROM fs_voltage_transformer;", con_user)

    err_voltage_dynamic_slide_window = pd.read_sql("SELECT * FROM err_voltage_dynamic_slide_window;", con_user)
    err_voltage_error_pile = pd.read_sql("SELECT * FROM err_voltage_error_pile;", con_user)
    err_voltage_matrix_status = pd.read_sql("SELECT * FROM err_voltage_matrix_status;", con_user)
    err_voltage_pre_pile_record = pd.read_sql("SELECT * FROM err_voltage_pre_pile_record;", con_user)
    err_voltage_slide_window = pd.read_sql("SELECT * FROM err_voltage_slide_window;", con_user)
    err_voltage_error = pd.read_sql("SELECT * FROM err_voltage_error;", con_user)
    con_user.close()

    # 将dataframe导出为csv
    algo_config.to_csv(
        "./csv_results/{}/{}_algo_config_{}.csv".format(scene_name, scene_name, str_now_time))
    da_voltage_data_temp.to_csv(
        "./csv_results/{}/{}_da_voltage_data_temp_{}.csv".format(scene_name, scene_name, str_now_time))
    dv_voltage_channel.to_csv(
        "./csv_results/{}/{}_dv_voltage_channel_{}.csv".format(scene_name, scene_name, str_now_time))
    dv_voltage_line_group_channel.to_csv(
        "./csv_results/{}/{}_dv_voltage_line_group_channel_{}.csv".format(scene_name, scene_name, str_now_time))
    dv_voltage_line_group.to_csv(
        "./csv_results/{}/{}_dv_voltage_line_group_{}.csv".format(scene_name, scene_name, str_now_time))
    fs_voltage_transformer.to_csv(
        "./csv_results/{}/{}_fs_voltage_transformer_{}.csv".format(scene_name, scene_name, str_now_time))

    err_voltage_dynamic_slide_window.to_csv(
        "./csv_results/{}/{}_err_voltage_dynamic_slide_window_{}.csv".format(scene_name, scene_name, str_now_time))
    err_voltage_error_pile.to_csv(
        "./csv_results/{}/{}_err_voltage_error_pile_{}.csv".format(scene_name, scene_name, str_now_time))
    err_voltage_matrix_status.to_csv(
        "./csv_results/{}/{}_err_voltage_matrix_status_{}.csv".format(scene_name, scene_name, str_now_time))
    err_voltage_pre_pile_record.to_csv(
        "./csv_results/{}/{}_err_voltage_pre_pile_record_{}.csv".format(scene_name, scene_name, str_now_time))
    err_voltage_slide_window.to_csv(
        "./csv_results/{}/{}_err_voltage_slide_window_{}.csv".format(scene_name, scene_name, str_now_time))
    err_voltage_error.to_csv(
        "./csv_results/{}/{}_err_voltage_error_{}.csv".format(scene_name, scene_name, str_now_time))


def write_txt(content):
    """
    写入结果到txt
    """
    with open(txt_path, "a") as f:
        f.write(content)


def all_flow(ip, port, username, DB_NAME, dir_path, scene_name, algorithm_type):
    """
    全流程测试
    :param ip:
    :param port:
    :param username:
    :param DB_NAME:
    :param v_arg_list:
    :param channel_config:
    :return:
    """
    result = "fail"
    try:
        print("{}-{}：开始测试,测试开始时间：{}".format(algorithm_type, scene_name,
                                                      datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
        DB_HOST = ip

        # 第一步,获取站端网页每日密码
        password = get_web_password(ip, port=10088, username="oetsky", password="oetsky@87942")
        print("{} ：web端访问密码".format(ip), password)
        print("第一步,获取站端网页每日密码完毕")

        # 第二步，停止站端计算（防止上个用例没计算完）
        stop_calc(ip, port, password)
        print("第二步,停止站端计算完毕")

        # 第三步，导入线路、采集数据、算法配置表
        # 由于csv_to_sql小概率性导入SQL出现异常，增加失败重试机制
        min_time = None
        max_time = None
        max_try = 5
        for i in range(max_try):
            try:
                min_time, max_time = csv_to_sql(ip, dir_path + "\\" + scene_name)
                break
            except:
                print("csv导入sql出现异常，当前异常次数：{}，文件名：{}".format(i + 1, dir_path + "\\" + scene_name))
                if i == max_try - 1:
                    assert False, "到达最大重试次数，次数：{}".format(max_try)
        datatime_max = datetime.datetime.strptime(max_time[:10], "%Y-%m-%d")
        print("第三步,导入线路、采集数据、算法配置表完毕")

        # 第四步,触发定时任务
        start_time_task(ip, port, username, password, min_time, max_time)
        print("第四步,触发定时任务完毕")

        # 第五步，等待定时任务执行完成
        wait_task_done(DB_HOST, DB_NAME, datatime_max)
        print("第五步，等待定时任务执行完成")

        # 第六步，导出csv
        export_csv(DB_HOST, DB_NAME, algorithm_type + scene_name)
        print("第六步，导出csv完毕")

        # 第七步，检查测试结果
        check_csv_result(dir_path, scene_name)
        print("第七步，检查测试结果完毕")

        result = "pass"
    except Exception as e:
        print(e)
        print("出现异常：{}".format(scene_name))
    finally:
        write_txt(algorithm_type + scene_name + ":" + result + "\r")


if __name__ == '__main__':
    # ip = "192.168.31.13"
    # port = "8082"
    # all_flow(ip, port, "admin", DB_NAME, r"E:\汤嘉提供\算法测试\通用算法", "2V0_互感器档案信息01", "通用算法")
    export_csv("192.168.31.13", DB_NAME, "自适应算法" + "9V4【并-并-并-同-同,分,分,停,停】V【并-并，并-并】")
